﻿/*
 * 文件名:Checking.cs
 * 类名:XHCTMS.DAL.Checking
 * 创建人: 仇祖萍
 * 重写: 杨航
 * 创建时间: 2013-5-21 19:34:30
 *  
 * 最近更新：2013-7-4 
 * 备注: 
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 对考勤表进行考勤和查询操作
    /// </summary>
    public class Checking
    {
        /// <summary>
        /// 分页获取考勤信息
        /// </summary>
        /// <param name="pageSize">页面大小</param>
        /// <param name="pageIndex">当前页号</param>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable Paging(int pageSize, int pageIndex)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append("proc_Checking");

            SqlParameter[] paras = {
                                       new SqlParameter("@PageSize", SqlDbType.Int),
                                       new SqlParameter("@PageIndex", SqlDbType.Int)
                                   };

            paras[0].Value = pageSize;
            paras[1].Value = pageIndex;

            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        /// <summary>
        /// 每日考勤
        /// </summary>
        /// <param name="checking">考勤信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int AddChecking(MODEL.Checking checking)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append(@"insert into checking values(@CheckingTeachingId,@CheckingScore,@CheckingReason,@CheckingStudentId, @CheckingTime)");

            SqlParameter[] paras = { 
                                       new SqlParameter("@CheckingTeachingId", SqlDbType.Int),
                                       new SqlParameter("@CheckingScore", SqlDbType.Decimal),
                                       new SqlParameter("@CheckingReason", SqlDbType.NVarChar, 32),     
                                       new SqlParameter("@CheckingStudentId", SqlDbType.Int),
                                       new SqlParameter("@CheckingTime", SqlDbType.NVarChar, 32)
                                   };

            paras[0].Value = checking.CheckingTeachingId;
            paras[1].Value = checking.CheckingScore;
            paras[2].Value = checking.CheckingReason;
            paras[3].Value = checking.CheckingStudentId;
            paras[4].Value = checking.CheckingCTime;

            return new DBHELP.Common().ExcuteQuery(queryString.ToString(), paras);
        }

        /// <summary>
        /// 查询考勤信息
        /// </summary>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable View(int teachingId)
        {
            StringBuilder queryString = new StringBuilder().Append(@"
                                                                select student_id, xhstudent_Id, student_name,teaching_id, teaching_lesson
                                                                from student, teaching 
                                                                where teaching_id=@TeachingId and teaching_class_Id=student_class_Id");
            SqlParameter[] paras = {                                       
                                       new SqlParameter("@TeachingId", SqlDbType.Int)
                                   };            
            paras[0].Value = teachingId;
            return new DBHELP.Common().GetDataTable(queryString.ToString(), paras);
        }

        /// <summary>
        /// 按条件查询考勤信息，分页显示结果
        /// </summary>
        /// <param name="pageIndex">页号</param>
        /// <param name="query"></param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable SelectByCondition(int pageIndex,int query)
        {
            StringBuilder queryString = new StringBuilder();

            queryString.Append(@"  select top 50
         checking.checking_Id, class.class_name, student.student_name,teaching.teaching_lesson,checking.score,checking.reason,checking.c_time 
         from 
         checking,class,student,teaching 
         where 
         checking.class_id=class.class_id and checking.student_id=student.student_id and checking.teaching_Lesson_id=teaching.teaching_id
         not in
         (select top (50*@pageindex) checking.checking_Id 
         from 
         checking,class,student,teaching 
         where 
         checking.class_id=class.class_id and checking.student_id=student.student_id and checking.teaching_Lesson_id=teaching.teaching_id
         and(student.student_name like '%'+@Query+'%'or class.class_name like '%'+@Query+'%' or teaching.teaching_lesson like '%'+@Query+'%' 
         orchecking.score like '%'+@Query+'%' or checking.reason like '%'+@Query+'%' or checking.c_time  like '%'+@Query+'%')");

            SqlParameter[] Para ={               
                                     new SqlParameter("@PageIndex", SqlDbType.Int),
                                     new SqlParameter("@Query",SqlDbType.Int)
                                };

            Para[0].Value = pageIndex;
            Para[1].Value = query;

            return new DBHELP.Common().GetDataTable(queryString.ToString(), Para);
        }

        /// <summary>
        /// 查询结果集的数目
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>返回DataTable类型 null/异常</returns>
        public DataTable SumByResult(string query)
        {
            string queryString = (@" select COUNT(checking_id)  from checking,class,student,teaching 
            where 
            checking.class_id=class.class_id and checking.student_id=student.student_id and checking.teaching_Lesson_id=teaching.teaching_id
            and 
            (student.student_name like '%'+@Query+'%'or class.class_name like '%'+@Query+'%' or teaching.teaching_lesson like '%'+@Query+'%' 
            or checking.score like '%'+@Query+'%' or checking.reason like '%'+@Query+'%' or checking.c_time  like '%'+@Query+'%')");

            SqlParameter[] Para = { 
                                      new SqlParameter("@Query", SqlDbType.VarChar, 32) 
                                  };

            Para[0].Value = query;

            return new DBHELP.Common().GetDataTable(queryString, Para);

        }

        public string GetAmount()
        {
            string queryString = (" select COUNT(*)/5  from v_Checking");

            return new DBHELP.Common().GetDataScalar(queryString.ToString());

        }

        public DataTable Search(int classId, string time, int pageSize, int pageIndex)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"p_GetCheck");
            SqlParameter[] paras = {
                                       new SqlParameter("@ClassId", SqlDbType.Int),   
                                       new SqlParameter("@CheckingTime", SqlDbType.VarChar, 32),
                                       new SqlParameter("@PAGESIZE", SqlDbType.Int),
                                       new SqlParameter("@PAGEINDEX", SqlDbType.Int)
                                   };
            paras[0].Value = classId;
            paras[1].Value = time;
            paras[2].Value = pageSize;
            paras[3].Value = pageIndex;
            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        public string GetCountPage(int pageSize)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select case  
                                when COUNT(*)%@pageSize=0 
                                then COUNT(*)/@pageSize
                                else COUNT(*)/@pageSize+1 
                                end from (
                                select teaching_lesson, student_name, checking_reason, checking_c_time, teaching_id, student_id
                                from checking, student, teaching 
                                where checking_student_id=student_id and checking_teaching_id=teaching_id)as A");
            SqlParameter[] paras = {
                                       new SqlParameter("@pageSize", SqlDbType.Int)
                                   };
            paras[0].Value = pageSize;
            return new DBHELP.Common().GetDataScalar(queryString.ToString(), paras);
        }
        public DataTable GetCountPage(MODEL.Teaching teaching)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"P_GetPageCount");
            SqlParameter[] paras = {
                                        new SqlParameter("@TEACHINGID", SqlDbType.Int),
                                        new SqlParameter("@TEACHINGCLASSID", SqlDbType.Int),
                                        new SqlParameter("@TEACHERID", SqlDbType.Int),
                                        new SqlParameter("@TEACHINGLESSON", SqlDbType.NVarChar, 64),
                                        new SqlParameter("@TEACHINGSTATE", SqlDbType.Int)
                                   };

            paras[0].Value = teaching.TeachingId;
            paras[1].Value = teaching.TeachingClassId;
            paras[2].Value = teaching.TeachingTeachId;
            paras[3].Value = teaching.TeachingLesson;
            paras[4].Value = teaching.TeachingState;

            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        public DataTable GetCountPage(int classId, string time)
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"P_GetCheckCount");
            SqlParameter[] paras = {
                                        new SqlParameter("@ClassId", SqlDbType.Int),
                                        new SqlParameter("@Time", SqlDbType.VarChar, 32)
                                   };

            paras[0].Value = classId;
            paras[1].Value = time;
            return new DBHELP.Common().GetDataTableProc(queryString.ToString(), paras);
        }

        public DataTable GetSubName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select sub_id,sub_name from sub");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        public DataTable GetClassName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append("select class_id,class_name from class order by class_name");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        public DataTable GetTearchingName()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select teaching.teaching_id,[user].[user_name]
                                from teaching,[user]
                                where teaching_teacher_Id=[user_id]");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        public DataTable GetClassTeacher()
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(@"select class_teacher_id,[user_name]
                                from class,[user]
                                where class_teacher_id=[user_id] ");
            return new DBHELP.Common().GetDataTable(queryString.ToString());
        }

        #region DB
    //    create view v_Checking  

    //As  

    //Select 
    //checking.checking_Id, student.student_name,teaching.teaching_lesson,checking_score,checking_reason,checking_c_time 
    //From checking,student,teaching  

    //Where checking.checking_student_id=student.student_id and checking.checking_teaching_id=teaching.teaching_id

    //           CREATE PROC proc_Checking
    //        @PAGESIZE INT,					--页面的大小
    //        @PAGEINDEX INT					--当然的页号
    //        AS
    //        BEGIN
    //         WITH TEMP AS (
    //        SELECT ROW_NUMBER() OVER (ORDER BY Checking_id ASC)AS ROW, * FROM v_Checking)

    //         SELECT * FROM TEMP WHERE ROW BETWEEN 

    //        (@PAGEINDEX-1)*@PAGESIZE+1 AND (@PAGEINDEX-1)*@PAGESIZE+@PAGESIZE

    //        END




//        declare @query int select checking.checking_Id, class.class_name, 
//student.student_name,teaching.teaching_lesson,checking.score,checking.reason,checking.c_time from checking,class,student,teaching 
//where 
//checking.class_id=class.class_id and checking.student_id=student.student_id and checking.teaching_Lesson_id=teaching.teaching_id
//and
//(student.student_name like '%'+@Query+'%'
// or class.class_name like '%'+@Query+'%' or teaching.teaching_lesson like '%'+@Query+'%' or
 // checking.score like '%'+@Query+'%' or checking.reason like '%'+@Query+'%' or checking.c_time  like '%'+@Query+'%')
        #endregion

    }
}
